------------------------------------------------
-- @name: cbo_basic_terms
-- @author: dion cho
-- @description: basic terminology of CBO
------------------------------------------------

@capture_on 
set echo on

-- create objects
drop table t1 purge;
create table t1(c1 int, c2 char(1));

insert into t1
select 
  level, 
  case 
    when level between 1 and 5000 then 'A'
    when level between 5001 and 8000 then 'B'
    when level between 8001 and 9000 then 'C'
    when level between 9001 and 9800 then 'D'
    when level between 9801 and 10000 then 'E'
  end
from dual
connect by level <= 10000
;

commit;

exec dbms_stats.gather_table_stats(user, 't1', -
    method_opt=>'for columns c1 size 1 c2 size 5');

@tab_stat t1

explain plan for
select * 
from t1
;

@plan

explain plan for
select *
from t1
where c1 = :b1
;

@plan

explain plan for
select *
from t1
where c2 = :b1
;

@plan

explain plan for
select *
from t1
where c2 = 'A'
;

@plan

explain plan for
select * 
from t1
where c1 = :b1 and c2 = :b2
;

@plan

explain plan for
select *
from t1
where c2 like '%A%'
;

@plan

set echo off
@capture_off
